/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Entity;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author Trung Duc
 */
public class ConnectionSQL {
    private String url;
    private Connection conn;
    private ResourceBundle messages;
    private boolean connected;

    /**
     * 
     * @param messages 
     */
    public ConnectionSQL(ResourceBundle messages){
        this.messages = messages;
        conn = getConnection();
    }
    /**
     * 
     * @return 
     */
    public Connection getConnection(){
        Connection c = null;
        BufferedReader br = null;
        try{
            br = new BufferedReader(new FileReader("src\\" + messages.getString("databaseconnection")));
            String driver = br.readLine();
            url = br.readLine();
            String userName = br.readLine();
            String password = br.readLine();
            String databasename = br.readLine();
            url = url +";database="+ databasename;
            Class.forName(driver);
            c = DriverManager.getConnection(url,userName,password);
            connected = true;
        } catch (Exception ex) {
            connected = false;
            JOptionPane.showMessageDialog(null, "Connect database fail");
        }finally{
            try {
                br.close();
            } catch (IOException ex) {
                Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return c;
    }
    /**
     * 
     * @param password
     * @return 
     */
    public String EncryptPassword(String password){
        String s = "";
        try {
            MessageDigest md = MessageDigest.getInstance("MD5");
            md.update(password.getBytes());
            byte[] text = md.digest();
            s = new String(text);
        } catch (NoSuchAlgorithmException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return s;
    }
    /**
     * 
     * @param username
     * @param password
     * @return User
     */
    public User Login(String username,String passwordLogin){
        User u = null;
        if(connected){
        String checkPassword = EncryptPassword(passwordLogin);
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procLogin"));
            cs.setString(1,username);
            cs.setString(2,checkPassword);
            ResultSet rs = cs.executeQuery();
            if(!rs.next()) return u;
            else{
                int id = rs.getInt("User_ID");
                int priority = rs.getInt("Priority");
                String password = checkPassword;
                String firstName = rs.getString("First_Name");
                String lastName = rs.getString("Last_Name");
                boolean gender;
                char ch = rs.getString("Gender").charAt(0);
                if(ch == 'F'){
                    gender = false;
                }else{
                    gender = true;
                }
                String email = rs.getString("Email");
                java.sql.Date birthday = rs.getDate("Birthday");
                String phone = rs.getString("Phone");
                u = new User(id, username, password, priority, firstName, lastName, gender, email, birthday, phone);
                cs.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        }
        return u;
    }
    /**
     * 
     * @return 
     */
    public void ChangePassword(int id,String newPassword){
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procChangePassword"));
            cs.setInt(1, id);
            String passwordChange = EncryptPassword(newPassword);
            cs.setString(2, passwordChange);
            cs.executeUpdate();
            cs.close();
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    /**
     * 
     * @return 
     */
    public ArrayList<User> getUser(){
        ArrayList<User> listUser = new ArrayList<User>(); 
        
        try {
            CallableStatement sttm = conn.prepareCall(messages.getString("procGetUser"));
            ResultSet rs = sttm.executeQuery();
            while(rs.next()){
                int id = rs.getInt(messages.getString("userID"));
                String userName = rs.getString(messages.getString("userName"));
                int priority = rs.getInt(messages.getString("priority"));
                String password = rs.getString(messages.getString("password"));
                String firstName = rs.getString(messages.getString("firstName"));
                String lastName = rs.getString(messages.getString("lastName"));
                boolean gender;
                char ch = rs.getString(messages.getString("gender")).charAt(0);
                if(ch == 'F'){
                    gender = false;
                }else{
                    gender = true;
                }
                String email = rs.getString(messages.getString("email"));
                java.sql.Date birthday = rs.getDate(messages.getString("birthday"));
                String phone = rs.getString(messages.getString("phone"));
                User u = new User(id, userName, password, priority, firstName, lastName, gender, email, birthday, phone);
                listUser.add(u);
            }
            sttm.close();
            rs.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listUser;
    }
    /**
     * 
     * @param userID
     * @return 
     */
    public User getUserByID(int userID){
        User user = null;
        try {
            CallableStatement sttm = conn.prepareCall(messages.getString("procGetUserByID"));
            sttm.setInt(1, userID);
            ResultSet rs = sttm.executeQuery();
            if(rs.next()){
                int id = rs.getInt(messages.getString("userID"));
                String userName = rs.getString(messages.getString("userName"));
                int priority = rs.getInt(messages.getString("priority"));
                String password = rs.getString(messages.getString("password"));
                String firstName = rs.getString(messages.getString("firstName"));
                String lastName = rs.getString(messages.getString("lastName"));
                boolean gender;
                char ch = rs.getString(messages.getString("gender")).charAt(0);
                if(ch == 'F'){
                    gender = false;
                }else{
                    gender = true;
                }
                String email = rs.getString(messages.getString("email"));
                java.sql.Date birthday = rs.getDate(messages.getString("birthday"));
                String phone = rs.getString(messages.getString("phone"));
                user = new User(id, userName, password, priority, firstName, lastName, gender, email, birthday, phone);       
            }
            sttm.close();
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return user;
    }
    /**
     * 
     * @return 
     */
        public ArrayList<User> getUserByPriority(int priority){
        ArrayList<User> listUser = new ArrayList<User>(); 
        
        try {
            CallableStatement sttm = conn.prepareCall(messages.getString("procGetUserByPriority"));
            sttm.setInt(1, priority);
            ResultSet rs = sttm.executeQuery();
            while(rs.next()){
                int id = rs.getInt(messages.getString("userID"));
                String userName = rs.getString(messages.getString("userName"));
                String password = rs.getString(messages.getString("password"));
                String firstName = rs.getString(messages.getString("firstName"));
                String lastName = rs.getString(messages.getString("lastName"));
                boolean gender;
                char ch = rs.getString(messages.getString("gender")).charAt(0);
                if(ch == 'F'|| ch=='f'){
                    gender = false;
                }else{
                    gender = true;
                }
                String email = rs.getString(messages.getString("email"));
                java.sql.Date birthday = rs.getDate(messages.getString("birthday"));
                String phone = rs.getString(messages.getString("phone"));
                User u = new User(id, userName, password, priority, firstName, lastName, gender, email, birthday, phone);
                listUser.add(u);
            }
            sttm.close();
            rs.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listUser;
    }
    /*
     * save user to database
     */
    public int saveUser(User u){
        int result = 0;
        try {
            CallableStatement sttm = conn.prepareCall(messages.getString("procSaveUser"));
            sttm.registerOutParameter(1, Types.INTEGER);
            sttm.setString(2, u.getUserName());
            sttm.setInt(3, u.getPriority());
            sttm.setString(4, EncryptPassword(u.getPassword()));
            sttm.setString(5, u.getFirstName());
            sttm.setString(6, u.getLastName());
            sttm.setString(7, u.isGender() == true ? "M": "F");
            sttm.setString(8, u.getEmail());
            sttm.setDate(9, u.getBirthday());
            sttm.setString(10, u.getPhone());
            
            result = sttm.executeUpdate();
            
            u.setId(sttm.getInt(1));
            
            sttm.close();
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    
    /**
     * 
     * @return 
     */
    public List<Item> getItem(){
        List<Item> listItem = new ArrayList<Item>();
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procSelectItem"));
            ResultSet rs = cs.executeQuery();
            while(rs.next()){
                    int itemID = rs.getInt("Item_ID");
                    String itemName = rs.getString("Item_Name");
                    int quantity = rs.getInt("Quantity");
                    String unit = rs.getString("Unit");
                    float price = rs.getFloat("Price");
                    String categoryName = rs.getString("Category_Name");
                    Item item = new Item(itemID, itemName, quantity, unit, price, categoryName);
                    listItem.add(item);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listItem;
    }
    /**
     * 
     * @param itemID
     * @return 
     */
    public Item getItemByID(int itemID){
        Item item = null;
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procSelectItemByID"));
            cs.setInt(1, itemID);
            ResultSet rs = cs.executeQuery();
            if(rs.next()){
                    String itemName = rs.getString("Item_Name");
                    int quantity = rs.getInt("Quantity");
                    String unit = rs.getString("Unit");
                    float price = rs.getFloat("Price");
                    String categoryName = rs.getString("Category_Name");
                    item = new Item(itemID, itemName, quantity, unit, price, categoryName);     
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return item;
    }
    /**
     * 
     * @return 
     */
    public List<Category> getCategory(){
        List<Category> listCategory = new ArrayList<Category>();
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procSelectCategory"));
            ResultSet rs = cs.executeQuery();
            while(rs.next()){
                int categoryID = rs.getInt(messages.getString("categoryID"));
                String categoryName = rs.getString(messages.getString("categoryName"));
                Category c = new Category(categoryID,categoryName);
                listCategory.add(c);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listCategory;
    }
    /**
     * 
     * @param itemName
     * @return 
     */
    public List<Category> getCategoryByItemName(String itemName){
        List<Category> listCategory = new ArrayList<Category>();
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procSelectCategoryByItemName"));
            cs.setString(1, itemName);
            ResultSet rs = cs.executeQuery();
            while(rs.next()){
                int categoryID = rs.getInt(messages.getString("categoryID"));
                String categoryName = rs.getString(messages.getString("categoryName"));
                Category c = new Category(categoryID,categoryName);
                listCategory.add(c);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listCategory;
    }
    /**
     * 
     * @param itemName
     * @param billID
     * @return 
     */
    public List<Category> getCategoryByItemNameAndBillID(String itemName,int billID){
        List<Category> listCategory = new ArrayList<Category>();
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procSelectCategoryByItemNameAndBillID"));
            cs.setString(1, itemName);
            cs.setInt(2, billID);
            ResultSet rs = cs.executeQuery();
            while(rs.next()){
                int categoryID = rs.getInt(messages.getString("categoryID"));
                String categoryName = rs.getString(messages.getString("categoryName"));
                Category c = new Category(categoryID,categoryName);
                listCategory.add(c);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listCategory;
    }
    /**
     * 
     * @param name
     * @return 
     */
    public List<Item> getItemByCategoryName(String name){
        List<Item> listItem = new ArrayList<Item>();
        try {
            CallableStatement csCategory = conn.prepareCall(messages.getString("procSelectCategoryID"));
            csCategory.setString(1, name);
            ResultSet rsCategory = csCategory.executeQuery();
            int categoryID;
            if(!rsCategory.next()) 
                return null;
            else
                categoryID = rsCategory.getInt("Category_ID");
            CallableStatement csItem = conn.prepareCall(messages.getString("procSelectItemByCategoryID"));
            csItem.setInt(1,categoryID);
            ResultSet rsItem = csItem.executeQuery();
            while(rsItem.next()){
                    int itemID = rsItem.getInt("Item_ID");
                    String itemName = rsItem.getString("Item_Name");
                    int quantity = rsItem.getInt("Quantity");
                    String unit = rsItem.getString("Unit");
                    float price = rsItem.getFloat("Price");
                    String categoryName = rsItem.getString("Category_Name");
                    Item item = new Item(itemID, itemName, quantity, unit, price, categoryName);
                    listItem.add(item);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listItem;
    }
    /**
     * 
     * @param name
     * @return 
     */
    public List<Item> getItemByName(String name){
        List<Item> listItem = new ArrayList<Item>();
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procSelectItemByName"));
            cs.setString(1, name);
            ResultSet rs = cs.executeQuery();
            while(rs.next()){
                    int itemID = rs.getInt("Item_ID");
                    String itemName = rs.getString("Item_Name");
                    int quantity = rs.getInt("Quantity");
                    String unit = rs.getString("Unit");
                    float price = rs.getFloat("Price");
                    String categoryName = rs.getString("Category_Name");
                    Item item = new Item(itemID, itemName, quantity, unit, price, categoryName);
                    listItem.add(item);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listItem;
    }
    /**
     * 
     * @return 
     */
    public List<Bill> getBill(){
        List<Bill> listBill = new ArrayList<Bill>();
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procSelectBill"));
            ResultSet rs = cs.executeQuery();
            while(rs.next()){
                int id = rs.getInt(messages.getString("billID"));
                java.sql.Timestamp timestamp = rs.getTimestamp(messages.getString("billDate"));
                String fullName = rs.getString(messages.getString("billFirstName"))+ " " + rs.getString(messages.getString("billLastName"));
                String account = rs.getString(messages.getString("userName"));
                int status = rs.getInt(messages.getString("billStatus"));
                CallableStatement csTotal = conn.prepareCall(messages.getString("procTotalPrice"));
                csTotal.setInt(1, id);
                ResultSet rsTotal = csTotal.executeQuery();
                rsTotal.next();
                long time = timestamp.getTime();
                Date date = new Date(time);
                float total = rsTotal.getFloat(1);
                Bill bill = new Bill(id, account, fullName, date, total, status);
                listBill.add(bill);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listBill;
    }
    /**
     * 
     * @param billID
     * @return 
     */
    public List<BillDetail> getBillDetail(int billID){
        List<BillDetail> listBillDetail = new ArrayList<BillDetail>();
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procSelectBillDetail"));
            cs.setInt(1, billID);
            ResultSet rs = cs.executeQuery();
            while(rs.next()){
                int itemID = rs.getInt(messages.getString("itemID"));
                String itemName = rs.getString(messages.getString("billDetailItemName"));
                int quantity = rs.getInt(messages.getString("billDetailQuantity"));
                float prices = rs.getFloat(messages.getString("billDetailPrice"));
                String categoryName = rs.getString(messages.getString("categoryName"));
                BillDetail billDetail = new BillDetail(billID, itemID, itemName, categoryName,quantity, prices);
                listBillDetail.add(billDetail);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listBillDetail;
    }
    /**
     * 
     * @param itemName
     * @param categoryName
     * @return 
     */
    public Item getItemByItemNameAndCategoryName(String itemName,String categoryName){
        Item item = null;
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procSelectItemByItemAndCategoryName"));
            cs.setString(1,itemName);
            cs.setString(2, categoryName);
            ResultSet rs = cs.executeQuery();
            if(rs.next()){
            int itemID = rs.getInt("Item_ID");
            int quantity = rs.getInt("Quantity");
            String unit = rs.getString("Unit");
            float price = rs.getFloat("Price");
            item = new Item(itemID, itemName, quantity, unit, price, categoryName);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return  item;
    }
    public boolean checkItemNameInBill(String itemName,int billID){
        Boolean result = false;
        List<Category> listCategory =  getCategoryByItemNameAndBillID(itemName,billID);
        if(!listCategory.isEmpty()) result = true;
        return result;
    }
    /**
     * 
     * @param user
     * @return 
     */
    public int addBill(User user){
        int billID = 0;
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procAddBill"));
            cs.registerOutParameter(1,Types.INTEGER);
            cs.setInt(2,user.getId());
            cs.executeUpdate();
            billID = cs.getInt(1);
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return billID;
    }
    /**
     * 
     * @param bd
     * @return 
     */
    public boolean addBillDetail(BillDetail bd){
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procAddBillDetail"));
            cs.setInt(1, bd.getBillID());
            cs.setInt(2, bd.getItemID());
            cs.setInt(3, bd.getQuantity());
            cs.setFloat(4, bd.getPrice());
            if(cs.executeUpdate()!=0)return true;
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
      return false;  
    }
    /**
     * 
     * @param billID
     * @param itemID
     * @param quantity
     * @return 
     */
    public boolean removeBillDetail(int billID,int itemID,int quantity){
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procRemoveBillDetail"));
            cs.setInt(1, billID);
            cs.setInt(2, itemID);
            cs.setInt(3, quantity);
            if(cs.executeUpdate()!=0)return true;
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
      return false;  
    }
    /**
     * 
     * @param billID
     * @param itemID
     * @param quantity
     * @return 
     */
    public boolean updateBillDetail (int billID,int itemID,int quantity){
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procUpdateBillDetail"));
            cs.setInt(1, billID);
            cs.setInt(2, itemID);
            cs.setInt(3, quantity);
            if(cs.executeUpdate()!=0)return true;
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        
      return false; 
    }
    /**
     * 
     * @param billID
     * @param status 
     */
    public void changeStatusBill(int billID,int status){
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procChangeStatus"));
            cs.setInt(1, billID);
            cs.setInt(2,status);
            cs.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    /**
     * 
     * @param userID
     * @param date
     * @return 
     */
    public List<Bill> getBillByUser(int userID){
        List<Bill> list = new ArrayList<Bill>();
        CallableStatement cs;
        try {
            cs = conn.prepareCall(messages.getString("procSelectBillByUser"));
            cs.setInt(1, userID);
            ResultSet rs = cs.executeQuery();
            while(rs.next()){
                int id = rs.getInt(messages.getString("billID"));
                java.sql.Timestamp timestamp = rs.getTimestamp(messages.getString("billDate"));
                String fullName = rs.getString(messages.getString("billFirstName"))+ " " + rs.getString(messages.getString("billLastName"));
                String account = rs.getString(messages.getString("userName"));
                int status = rs.getInt(messages.getString("billStatus"));
                CallableStatement csTotal = conn.prepareCall(messages.getString("procTotalPrice"));
                csTotal.setInt(1, id);
                ResultSet rsTotal = csTotal.executeQuery();
                rsTotal.next();
                long time = timestamp.getTime();
                Date date = new Date(time);
                float total = rsTotal.getFloat(1);
                Bill bill = new Bill(id, account, fullName, date, total, status);
                list.add(bill);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }
    /**
     * 
     * @param userID
     * @param date
     * @return 
     */
    public int getBillID(int userID,java.util.Date date){
        int billID =0;
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procSelectBillID"));
            cs.setInt(1, userID);
            Timestamp timestamp = new Timestamp(date.getTime());
            cs.setTimestamp(2, timestamp);
            ResultSet rs = cs.executeQuery();
            if(rs.next()) billID = rs.getInt(1);
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return billID;
    }
    public boolean checkBillID(int billID){
        Bill bill = getBillByID(billID);
        if(bill == null) return false;
                else return true;
    }
    /**
     * 
     * @param billID
     * @return 
     */
    public Bill getBillByID(int billID){
        Bill bill = null;
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procSelectBillByID"));
            cs.setInt(1, billID);
            ResultSet rs = cs.executeQuery();
            if(rs.next()){
                java.sql.Timestamp timestamp = rs.getTimestamp(messages.getString("billDate"));
                String fullName = rs.getString(messages.getString("billFirstName"))+ " " + rs.getString(messages.getString("billLastName"));
                String account = rs.getString(messages.getString("userName"));
                int status = rs.getInt(messages.getString("billStatus"));
                CallableStatement csTotal = conn.prepareCall(messages.getString("procTotalPrice"));
                csTotal.setInt(1, billID);
                ResultSet rsTotal = csTotal.executeQuery();
                rsTotal.next();
                long time = timestamp.getTime();
                Date date = new Date(time);
                float total = rsTotal.getFloat(1);
                bill = new Bill(billID, account, fullName, date, total, status);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return bill;
    }
    /**
     * 
     * @param itemID
     * @param quantity 
     */
    public void cancelBillDetail(int itemID,int quantity){
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procCancelBillDetail"));
            cs.setInt(1, itemID);
            cs.setInt(2, quantity);
            cs.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    /**
     * 
     * @param billID
     * @return 
     */
    public boolean removeBill(int billID){
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procRemoveBill"));
            cs.setInt(1, billID);
            if(cs.executeUpdate()>0) return true;
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    /**
     * 
     * @param itemID
     * @param billID
     * @return 
     */
    public int getQuantityBillDetail(int itemID,int billID){
        int quantity =0;
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procSelectQuantityByItemIDBillID"));
            cs.setInt(1, itemID);
            cs.setInt(2, billID);
            ResultSet rs = cs.executeQuery();
            if(rs.next()){
                quantity = rs.getInt(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return quantity;
    }
    /**
     * 
     * @param userName
     * @return 
     */
    public int getUserID(String userName){
        int userID =0;
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procSelectUserID"));
            cs.setString(1, userName);
            ResultSet rs = cs.executeQuery();
            if(rs.next()) userID = rs.getInt(1);
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return userID;
    }
    /**
     * 
     * @param cate
     * @return 
     */
    public boolean saveCategory(Category cate){
        int result = -1;
        try {
            CallableStatement sttm = conn.prepareCall("{call Add_Category(?,?)}");
            sttm.registerOutParameter(1, Types.INTEGER);
            sttm.setString(2, cate.getName());
            result = sttm.executeUpdate();
            int id = sttm.getInt(1);
            cate.setID(id);
            sttm.close();
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        if(result == -1)
            return false;
        else
            return true;
    }
    /**
     * 
     * @param userID
     * @return 
     */
    public List<Receipt>  getReceiptByUser(int userID){
        List<Receipt> listReceipt = new ArrayList<Receipt>();
        try {
            CallableStatement  cs = conn.prepareCall(messages.getString("procSelectReceiptByUser"));
            cs.setInt(1, userID);
            ResultSet rs = cs.executeQuery();
            while(rs.next()){
            int itemID = rs.getInt(messages.getString("itemID"));
            int billID = rs.getInt(messages.getString("billID"));
            java.sql.Timestamp timestamp = rs.getTimestamp(messages.getString("receiptDate"));
            Date date = new java.util.Date(timestamp.getTime());
            int quantity = rs.getInt(messages.getString("receiptQuantity"));
            String userName = rs.getString(messages.getString("userName"));
            String itemName = rs.getString(messages.getString("itemNameDatabase"));
            String categoryName = rs.getString(messages.getString("categoryName"));
            Receipt receipt = new Receipt(billID, itemID, userID, date, quantity, userName, itemName, categoryName);
            listReceipt.add(receipt);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listReceipt;
    }
    /**
     * 
     * @param item
     * @return 
     */
    public boolean saveItem(Item item){
        int result = -1;
        try {
            CallableStatement sttm = conn.prepareCall("{call Add_Item(?,?,?,?,?,?)}");
            sttm.registerOutParameter(1, Types.INTEGER);
            sttm.setString(2, item.getName());
            sttm.setInt(3, item.getQuantity());
            sttm.setString(4, item.getUnit());
            sttm.setFloat(5, item.getPrice());
            sttm.setString(6, item.getCategory());
            result = sttm.executeUpdate();
            int id = sttm.getInt(1);
            item.setID(id);
            sttm.close();
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        if(result == -1)
            return false;
        else
            return true;
    }
    /**
     * 
     * @param item
     * @return 
     */
    public boolean updatePriceItem(Item item){
        int result = -1;
        try {
            CallableStatement sttm = conn.prepareCall("{call Update_Price_Item(?,?)}");
            sttm.setInt(1, item.getId());
            sttm.setFloat(2, item.getPrice());
            
            result = sttm.executeUpdate();
            
            sttm.close();
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        if(result == -1)
            return false;
        else
            return true;
        
    }
    /**
     * 
     * @param receipt
     * @return 
     */
     public boolean addReceipt(Receipt receipt){
         boolean result = false;
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procAddReceipt"));
            cs.setInt(1, receipt.getBillID());
            cs.setInt(2, receipt.getItemID());
            cs.setInt(3,receipt.getUserID());
            cs.setInt(4, receipt.getQuantity());
            if(cs.executeUpdate()>0) result = true;
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
     /**
      * 
      * @param billID
      * @param itemID
      * @return 
      */
     public Receipt getReceiptByBilIDAndItemID(int billID,int itemID){
         Receipt receipt = null;
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procSelectReceiptByBillIDItemID"));
            cs.setInt(1, billID);
            cs.setInt(2, itemID);
            ResultSet rs = cs.executeQuery();
            if(rs.next()){
            int userID =  rs.getInt(messages.getString("userID"));
            java.sql.Timestamp timestamp = rs.getTimestamp(messages.getString("receiptDate"));
            Date date = new java.util.Date(timestamp.getTime());
            int quantity = rs.getInt(messages.getString("receiptQuantity"));
            String userName = rs.getString(messages.getString("userName"));
            String itemName = rs.getString(messages.getString("itemNameDatabase"));
            String categoryName = rs.getString(messages.getString("categoryName"));
            receipt = new Receipt(billID, itemID, userID, date, quantity, userName, itemName, categoryName);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
         return receipt;
     }
     public List<BillDetail> getAllBillDetail(){
         List<BillDetail> list = new ArrayList<BillDetail>();
        try {
            CallableStatement sttm = conn.prepareCall("{call Select_All_Bill_Detail}");
            ResultSet rs = sttm.executeQuery();
            while(rs.next()){
                int billID = rs.getInt(messages.getString("billID"));
                int itemID = rs.getInt(messages.getString("itemID"));
                String itemName = rs.getString(messages.getString("billDetailItemName"));
                int quantity = rs.getInt(messages.getString("billDetailQuantity"));
                float prices = rs.getFloat(messages.getString("billDetailPrice"));
                String categoryName = rs.getString(messages.getString("categoryName"));
                BillDetail billDetail = new BillDetail(billID,itemID,itemName, categoryName, quantity, prices);
                list.add(billDetail);
            }
            
            sttm.close();
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
     }
     /**
      * 
      * @return 
      */
     public List<Receipt> getReceipt(){
         List<Receipt> list = new ArrayList<Receipt>();
        try {
            CallableStatement sttm = conn.prepareCall("{call Select_Receipt}");
            ResultSet rs = sttm.executeQuery();
            while(rs.next()){
                int itemID = rs.getInt(messages.getString("itemID"));
                int billID = rs.getInt(messages.getString("billID"));
                java.sql.Timestamp timestamp = rs.getTimestamp(messages.getString("receiptDate"));
                Date date = new java.util.Date(timestamp.getTime());
                int quantity = rs.getInt(messages.getString("receiptQuantity"));
                String userName = rs.getString(messages.getString("userName"));
                String itemName = rs.getString(messages.getString("itemNameDatabase"));
                String categoryName = rs.getString(messages.getString("categoryName"));
                Receipt receipt = new Receipt(billID, itemID, itemID, date, quantity, userName, itemName, categoryName);
                list.add(receipt);
            }
            sttm.close();
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
     }
     /**
      * 
      * @param billID
      * @param itemID
      * @param quantity
      * @return 
      */
     public boolean updateReceipt(int billID,int itemID, int quantity){
         boolean result = false;
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procEditReceipt"));
            cs.setInt(1, billID);
            cs.setInt(2, itemID);
            cs.setInt(3,quantity);
            if(cs.executeUpdate()!=0) result = true;
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
         return result;
     }
     /**
      * 
      * @param report
      * @return 
      */
     public boolean sendReport(Report report){
         boolean result = false;
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procAddReport"));
            cs.setInt(1, report.getSenderID());
            cs.setInt(2, report.getReveiceID());
            cs.setString(3, report.getMessages());
            if(cs.executeUpdate()!=0) result = true;
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
         return result;
     }
     /**
      * 
      * @return 
      */
     public List<Report> getReportsByReceiver(int receiverID){
         List<Report> listReport = new ArrayList<Report>();
        try {
            CallableStatement csReport = conn.prepareCall(messages.getString("procSelectReportByReceiverID"));
            csReport.setInt(1, receiverID);
            ResultSet rsReport = csReport.executeQuery();
            while(rsReport.next()){
                int senderID = rsReport.getInt(messages.getString("reportSenderID"));
                String message = rsReport.getString(messages.getString("reportMessage"));
                int status = rsReport.getInt(messages.getString("reportStatus"));
                int reportID = rsReport.getInt(messages.getString("reportID"));
                Timestamp timeStamp = rsReport.getTimestamp(messages.getString("reportDate"));
                Date date = new Date(timeStamp.getTime());
                User userSender = this.getUserByID(senderID);
                listReport.add(new Report(reportID,senderID, receiverID, message, date, status, userSender.getUserName()));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
         return listReport;
     }
     public void changeStatusReport(int reportID){
        try {
            CallableStatement cs = conn.prepareCall(messages.getString("procChangeStatusReport"));
            cs.setInt(1, reportID);
            cs.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
         
     }
     /**
      * 
      * @param beginDate
      * @param endDate
      * @return 
      */
     public List<Item> getItemSold(java.sql.Date beginDate,java.sql.Date endDate){
         List<Item> listItem = new ArrayList<Item>();
        try {
            CallableStatement csBillDetail = conn.prepareCall(messages.getString("procStatisticsByDate"));
            csBillDetail.setDate(1, beginDate);
            csBillDetail.setDate(2, endDate);
            ResultSet rsBilLDetail = csBillDetail.executeQuery();
            while(rsBilLDetail.next()){
                int itemID = rsBilLDetail.getInt(messages.getString("itemID"));
                int quantity = rsBilLDetail.getInt(messages.getString("billDetailQuantity"));
                float total = rsBilLDetail.getFloat(messages.getString("billTotal"));
                Item item = this.getItemByID(itemID);
                Item itemSold = new Item(itemID, item.getName(), quantity, item.getUnit(), total, item.getCategory());
                listItem.add(itemSold);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
         return listItem;
     }
    /**
     * 
     * @return 
     */
    public String getDatabaseInfo(){
        try {
            DatabaseMetaData meta= conn.getMetaData();
            return meta.getDatabaseProductName() + meta.getDatabaseProductVersion();
            
        } catch (SQLException ex) {
            Logger.getLogger(ConnectionSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
}
